# -*- coding: utf-8 -*-  
from waitress import serve
from flask import Flask, Response, request, render_template,send_from_directory
import json
from datetime import date, datetime, timedelta
from sqlite_util import SQLiteHelper 
import logging
from logging.handlers import RotatingFileHandler
import os
 

app = Flask(__name__, static_folder='dist')


@app.route('/', defaults={'path': ''})
@app.route('/<path:path>')
def catch_all(path):
    if path != "" and path.endswith(".html"):
        return send_from_directory('dist', 'index.html')
    else:
        return send_from_directory('dist', path)

def getTotalAmount():
    db_helper = SQLiteHelper('example.db')
    try:
        today = datetime.now()
        year = today.strftime("%Y")
        year_first = year + "-01-01"
        year_end = year + "-12-31"
        total_amount_sql = "SELECT sum(amount) FROM t_sale where create_time <= '" + year_end + "'" + " AND create_time >= '" + year_first + "'"
        result = db_helper.execute_query(total_amount_sql)
        return result[0][0]
    except Exception as e:
        print(e)
    finally:
        db_helper.close_connection()

def getSaleTarget():
    db_helper = SQLiteHelper('example.db')
    try:
        target_sql = "SELECT target FROM t_company"
        result = db_helper.execute_query(target_sql)
        return result[0][0]
    except Exception as e:
        print(e)
    finally:
        db_helper.close_connection()

def getShopList():
    shop_list = []
    db_helper = SQLiteHelper('example.db')
    try:
        shop_sql = "SELECT name FROM t_shop"
        result_list = db_helper.execute_query(shop_sql)
        for item in result_list:
            shop_list.append(item[0])
    except Exception as e:
        print(e)
    finally:
        db_helper.close_connection()
    return shop_list

def getSaleRecord(name,day):
    db_helper = SQLiteHelper('example.db')
    try:
        sale_sql = "SELECT * FROM t_sale where name = '" + name + "'" + " and day = '" + day + "'"
        result = db_helper.execute_query(sale_sql)
    except Exception as e:
        print(e)
    finally:
        db_helper.close_connection()
    return result[0]

def updateSaleTarget(target):
    db_helper = SQLiteHelper('example.db')
    try:
        target_sql = "update t_company set target = ?"
        db_helper.execute_non_query(target_sql,(target))
    except Exception as e:
        print(e)
    finally:
        db_helper.close_connection()

def getRecentDayAmount(i):
    db_helper = SQLiteHelper('example.db')
    try:
        lastday = getRecentDate(i+1).strftime("%Y-%m-%d")
        day_amount_sql = "SELECT sum(amount) FROM t_sale where day = '" + lastday + "'"
        result = db_helper.execute_query(day_amount_sql)
        return result[0][0]
    except Exception as e:
        print(e)
    finally:
        db_helper.close_connection()

def getStoreAmount():
    db_helper = SQLiteHelper('example.db')
    store_list = []
    try:
        today = datetime.now()
        year = today.strftime("%Y")
        year_first = year + "-01-01"
        year_end = year + "-12-31"
        store_amount_sql = "SELECT name,sum(amount) FROM t_sale where day <= '" + year_end + "'" + " AND day >= '" + year_first + "'" + " group by name"
        result_list = db_helper.execute_query(store_amount_sql)
        for item in result_list:
            store_list.append({"name":item[0],"value":item[1]})
        return store_list
    except Exception as e:
        print(e)
    finally:
        db_helper.close_connection()

def get_date_range(start_date, end_date, date_format='%Y-%m-%d'):
    # 将字符串解析为 date 对象
    start = datetime.strptime(start_date, '%Y-%m-%d').date()
    end = datetime.strptime(end_date, '%Y-%m-%d').date()
    
    # 校验日期顺序
    if end < start:
        raise ValueError("结束日期不能早于起始日期")
    
    # 生成日期列表
    delta = (end - start).days
    return [(start + timedelta(days=i)).strftime(date_format) for i in range(delta + 1)]

@app.route('/data-screen/insertDaySale', methods=['POST'])
def insertDaySale():
    result = ""
    sales = request.json
    app.logger.info('insertDaySale - 销售记录: %s',json.dumps(sales,ensure_ascii=False))
    db_helper = SQLiteHelper('example.db')
    
    try:
        today = datetime.now()
        for sale in sales:
            if sale['name'] and sale['day'] and sale['amount']:
                if sale['name'] in getShopList():
                    saleRecord = getSaleRecord(sale['name'],sale['day'])
                    if saleRecord:
                        update_sql = 'update t_sale set amount = ? where name = ? and day = ?'
                        db_helper.execute_non_query(update_sql, (sale['amount'],sale['name'], sale['day']))
                        result = "更新成功"
                    else:
                        insert_sql = 'INSERT INTO t_sale (name, day,amount,create_time) VALUES (?, ?, ?, ?)'
                        db_helper.execute_non_query(insert_sql, (sale['name'], sale['day'],sale['amount'],today))
                        result = "录入成功"
                else:
                    result = sale['name'] + "店铺不存在"
            else:
                result = "数据缺失"
    except Exception as e:
        result = "录入失败"
        print(e)
    finally:
        db_helper.close_connection()
    return Response(json.dumps(result,ensure_ascii=False),  mimetype='application/json')
    

@app.route('/data-screen/getRankBoard', methods=['GET'])
def getRankBoard():
    app.logger.info('getRankBoard')
    result = {"data":getStoreAmount(),"rowNum":8}
    return Response(json.dumps(result,ensure_ascii=False),  mimetype='application/json')


def getAnnualErrorDate():
    db_helper = SQLiteHelper('example.db')
    try:
        today = datetime.now()
        nowyear = today.strftime("%Y")
        nowday = today.strftime("%Y-%m-%d")
        total_date = get_date_range(nowyear + '-01-01', nowday)
        current_date = []
        day_sql = "select day from t_sale where day <= " + nowday
        result_list = db_helper.execute_query(day_sql)
        for item in result_list:
            current_date.append(item[0])
        current_set = set(current_date)
        error_date = [x for x in total_date if x not in current_set]
        return error_date
    except Exception as e:
        print(e)
    finally:
        db_helper.close_connection()

@app.route('/data-screen/getAnnualSale', methods=['GET'])
def getAnnualSale():
    app.logger.info('getAnnualSale')
    amount = getTotalAmount()
    if amount is None:
        amount = 0 
    target = getSaleTarget()
    percentage = (float(amount)/target)*100
    error_date = getAnnualErrorDate()
    result = {"sale":{"amount":amount,"target":target,"error_date":error_date},"config":{"data":[round(percentage)],"shape":"round"}}
    return Response(json.dumps(result,ensure_ascii=False),  mimetype='application/json')

def getRecentDate(days):
    today = datetime.now()
    recentDay = today - timedelta(days=days)
    return recentDay
    
@app.route('/data-screen/getWeekSale', methods=['GET'])
def getWeekSale():
    app.logger.info('getWeekSale')
    date_range = []
    amount_range = []
    error_date = []
    for i in range(5):
        amount = getRecentDayAmount(i)
        if amount:
            amount_range.append(amount)
            date = getRecentDate(i+1).strftime("%m-%d")
        else:
            amount_range.append(0)
            date = getRecentDate(i+1).strftime("%m-%d")
            error_date.append(date)
        date_range.append(date)
    result = {"error_date":error_date,"color": ["#37a2da"],"title":{"text": "最近一周销售额趋势","style": {"fill": "#fff"}}, 
            "xAxis":{"name": "日期","data":date_range,"nameTextStyle": {"fill": "#fff","fontSize": 15},"axisLabel": {"style": {"fill": "#fff","fontSize": 10}}},
            "yAxis":{"name": "累计销售额(元)","min": 0,"data":"value","nameTextStyle": {"fill": "#fff","fontSize": 15},"axisLabel": {"style": {"fill": "#fff","fontSize": 10}}},
            "series":[{"data":amount_range,"type":"bar"}]}
    return Response(json.dumps(result,ensure_ascii=False),  mimetype='application/json')


if __name__ == '__main__':
    current_dir = os.getcwd()
    logging.basicConfig(level=logging.INFO)
    file_handler = RotatingFileHandler(current_dir + '/datascreen.log', maxBytes=1024 * 1024 * 10, backupCount=10)
    formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(filename)s - %(funcName)s - %(lineno)s - %(message)s')
    file_handler.setFormatter(formatter)
    app.logger.addHandler(file_handler)
    #app.run(host="0.0.0.0",port=8000)
    serve(app,host="0.0.0.0",port=8000)